﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_REPORT_Fit3012p_Get_Monthly_Total_Result_By_Training_Part_N_Type')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_REPORT_Fit3012p_Get_Monthly_Total_Result_By_Training_Part_N_Type
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_REPORT_Fit3012p_Get_Monthly_Total_Result_By_Training_Part_N_Type
**	Desc : 월별 운동부위별 운동별 트레이닝 결과를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_FIT_REPORT_Fit3012p_Get_Monthly_Total_Result_By_Training_Part_N_Type '2014-06', 'AB001', 'TT001', 'ko'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit3012p
**	Auth : 송시명
**	Date : 2014-06-19
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_REPORT_Fit3012p_Get_Monthly_Total_Result_By_Training_Part_N_Type]
@ls_base_ym nvarchar(7),
@ls_training_part nvarchar(5),
@ls_training_type_id nvarchar(5),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(C.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       C.Training_Nm,
	   A.Training_Id,
       A.Training_Dt,
       A.Start_Hour + ':' + A.Start_Minute + ' ~ ' + A.End_Hour + ':' + A.End_Minute AS Training_Time,
       B.Action_Weight,
       B.Action_Count,
       B.Set_Count,
       C.View_Order
  FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
  JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
  JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
 WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
   AND C.Training_Part LIKE @ls_training_part + '%'
   AND C.Training_Type_Id LIKE @ls_training_type_id + '%'
UNION ALL
--소계 (운동명)
SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(C.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       C.Training_Nm,
       '' AS Training_Id,
	   dbo.ufn_UPDMS_Get_Code_Name('BA001', 'FIT', @ls_lang_set) AS Training_Dt,
       '' AS Training_Time,
       SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
       SUM(B.Action_Count * B.Set_Count) AS Total_Count,
       SUM(B.Set_Count) AS Set_Count,
       MAX(C.View_Order) + 'X'
  FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
  JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
  JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
 WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
   AND C.Training_Part LIKE @ls_training_part + '%'
   AND C.Training_Type_Id LIKE @ls_training_type_id + '%'
 GROUP BY Training_Part, C.Training_Nm
UNION ALL
--소계 (운동부위)
SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(C.Training_Part, 'FIT', @ls_lang_set) AS Training_Part_Nm,
       dbo.ufn_UPDMS_Get_Code_Name('BA001', 'FIT', @ls_lang_set) AS Training_Nm,
	   '' AS Training_Id,
       '' AS Training_Dt,
       '' AS Training_Time,
       SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
       SUM(B.Action_Count * B.Set_Count) AS Total_Count,
       SUM(B.Set_Count) AS Set_Count,
       'BA001'
  FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
  JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
  JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
 WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
   AND C.Training_Part LIKE @ls_training_part + '%'
   AND C.Training_Type_Id LIKE @ls_training_type_id + '%'
 GROUP BY Training_Part
UNION ALL
--총계
SELECT 'BA002',
       dbo.ufn_UPDMS_Get_Code_Name('BA002', 'FIT', @ls_lang_set) AS Training_Part_Nm,
	   '' AS Training_Nm,
       '' AS Training_Id,
       '' AS Training_Dt,
       '' AS Training_Time,
       SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
       SUM(B.Action_Count * B.Set_Count) AS Total_Count,
       SUM(B.Set_Count) AS Set_Count,
       'BA002'
  FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
  JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
  JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
 WHERE LEFT(A.Training_Dt, 7) = @ls_base_ym
   AND C.Training_Part LIKE @ls_training_part + '%'
   AND C.Training_Type_Id LIKE @ls_training_type_id + '%'
 ORDER BY Training_Part, View_Order, A.Training_Dt

GO